/************************************************************************************
 * @File name   :      DealerMacDaoImpl.java
 *
 * @Author      :      Wu Bin
 *
 * @Date        :      2011-6-17
 *
 * @Copyright Notice: 
 * Copyright (c) 2014 DMS, Inc. All  Rights Reserved.
 * This software is published under the terms of the DMS Software
 * License version 1.0, a copy of which has been included with this
 * distribution in the LICENSE.txt file.
 * 
 * 
 * ----------------------------------------------------------------------------------
 * Date                          Who              Version               Comments
 * 2011-6-17 ä¸‹å�ˆ02:59:15             Wu Bin          1.0             Initial Version
 * 2011-9-30 ä¸‹å�ˆ02:18:17             Wu Bin          1.1             modify queryDealerWithPaging(getDealerName)
 * 2011-10-19 ä¸Šå�ˆ8:48:07             Wu Jianxi       1.2             add mac org_type = 3 condition
 * 2011-10-26 ä¸‹å�ˆ05:03:50            Wu Jianxi       1.3             fix bug
 * 2011-10-28 ä¸Šå�ˆ11:21:37            Wu Jianxi       1.4             fix bug
 * 2011-11-3 ä¸Šå�ˆ10:30:28             Wu Jianxi       1.5             modify queryDealerWithPaging to fix count sql bug
 * 2011-12-20              Wangzhize       1.6             modify queryDealerWithPaging add order by dealercode desc
 ************************************************************************************/
package com.cap.bts.framework.common.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.inject.Inject;

import com.cap.bts.framework.common.dao.IDealerMacDao;
import com.cap.bts.framework.common.dto.DealerQueryDto;
import com.cap.bts.framework.common.dto.DealerQueryResultDto;
import com.cap.bts.framework.common.dto.MacQueryDto;
import com.cap.bts.framework.common.dto.MacQueryResultDto;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.cap.bts.commonfunc.ge.dealer.constant.DealerStatus;
import com.cap.bts.commonfunc.ge.dealer.domain.DealerInfo;
import com.cap.bts.framework.common.dao.callback.NativeQueryWithPagingJpaCallback;
import com.cap.bts.framework.common.dao.callback.QueryWithPagingJpaCallback;
import com.cap.bts.framework.common.dao.impl.BaseDaoImpl;
import com.cap.bts.framework.common.exception.AppException;
import com.cap.bts.framework.common.util.Utils;
import com.cap.bts.framework.usermgnt.constant.OrganizationType;

/**
 *
 */
@Repository("dealerMacDao")
public class DealerMacDaoImpl extends BaseDaoImpl<DealerInfo, Long> implements IDealerMacDao {

    /**
     * @Date        :      2011-6-17 
     * @param clazz   
     */
    @Inject
    public DealerMacDaoImpl(
        @Value("com.cap.bts.commonfunc.ge.dealer.domain.DealerInfo") Class<DealerInfo> clazz) {
        super(clazz);
    }

    
    /** 
     * overridden:
     * @Date        :      2011-6-17
     * @see com.cap.bts.gdms.dao.IDealerMacDao#queryDealerWithPaging(com.cap.bts.gdms.dto.DealerQueryDto)
    **/
    @SuppressWarnings("unchecked")
    @Override
    public List<DealerQueryResultDto> queryDealerWithPaging(DealerQueryDto dealerQueryDto) {
        
        String countSqlStart = "select count(*) from ( ";
        
        String selectClause = "select X.id, X.dealer_code, X.dealer_abbre_name," +
                "X.rfs_org_id, X.Rfs_Org_Code, X.rfs_org_name, " +
                "LISTAGG(X.mac_org_name,',') within group (order by X.mac_org_name) as mac_org_names, "+
                "x.is_virtual_dealer";
        
        //2012-6-30 è¿½åŠ æ˜¯å�¦è™šæ‹Ÿç»�é”€å•†æ˜¾ç¤ºåˆ— is_virtual_dealer ,group by is_virtual_dealer
        StringBuilder strFrom = new StringBuilder();
        strFrom
            .append(
                " from ( select a.id as id, a.dealer_code as dealer_code, a.dealer_abbre_name as dealer_abbre_name, a.is_virtual_dealer as is_virtual_dealer, "
                    + " d.id as rfs_org_id, d.org_code as rfs_org_code, d.org_name as rfs_org_name, c.org_name as mac_org_name from tm_dealer a  left join ("
                    + " select b1.dealer_org_id,b2.id,b2.org_code,b2.parent_org_code,b2.org_name from tm_common_org_dealer b1,tm_common_organization b2"
                    + " where b1.sgm_org_id = b2.id and b2.org_type = 3 ) c on a.dealer_org_id =c.dealer_org_id " + " left join tm_common_organization d  on c.parent_org_code = d.org_code ")
        .append(" where  a.brand_id=").append(dealerQueryDto.getBrandId());
        
        if(null != dealerQueryDto.getCurrentDealerId()){
            strFrom.append(" and a.id <> ").append(dealerQueryDto.getCurrentDealerId());
        }
        
        if (Utils.notEmpty(dealerQueryDto.getDealerCode())) {
            strFrom.append(" and a.dealer_code = '" + dealerQueryDto.getDealerCode() + "'");
        }
        if (Utils.notEmpty(dealerQueryDto.getDealerName())) {
            strFrom.append(" and a.dealer_abbre_name like '" + dealerQueryDto.getDealerName() + "%'");
        }
        if (Utils.notEmpty(dealerQueryDto.getOrgCode())) {
            if (!"-1".equals(dealerQueryDto.getOrgCode())) {
                strFrom.append(" and d.org_code = '" + dealerQueryDto.getOrgCode() + "'");
            }
        }
        if (Utils.notEmpty(dealerQueryDto.getMacCode())){
            strFrom.append(" and c.org_code = '" + dealerQueryDto.getMacCode() + "'");
        }
        strFrom.append(" and a.dealer_Status <> '"+DealerStatus.READY.getValue()+"' and a.dealer_Status <> '"+DealerStatus.SHUTDOWN.getValue()+"'");
        
        strFrom.append(") x group by X.id, X.dealer_code, X.dealer_abbre_name, x.is_virtual_dealer, X.rfs_org_id, X.Rfs_Org_Code, X.rfs_org_name ");
        //æŽ’åº�
        String orderStr = " order by  X.dealer_code desc ";
        
        String countSqlEnd = " )";
        
        logger.debug("List<DealerQueryResultDto> query countHql: " + countSqlStart + strFrom.toString() );
        logger.debug("List<DealerQueryResultDto> query hql: " + selectClause + strFrom.toString() + orderStr);

        String selectSql = selectClause + strFrom.toString()+ orderStr;
        String countSql = countSqlStart + selectClause + strFrom.toString() + countSqlEnd;
        List<Object[]> list = getJpaTemplate().executeFind(
                new NativeQueryWithPagingJpaCallback<Object[]>(selectSql, countSql, dealerQueryDto.getPager()));
        
        List<DealerQueryResultDto> listDto = new ArrayList<DealerQueryResultDto>();
        for(int i = 0; i < list.size(); i++){
            DealerQueryResultDto dto = new DealerQueryResultDto();
            Object[] obj = (Object[])list.get(i);
            dto.setDealerId(((BigDecimal)obj[0]).longValue());
            dto.setDealerCode((String)obj[1]);
            dto.setDealerName((String)obj[2]);
            if (obj[3] != null) {
                dto.setRfsId(((BigDecimal)obj[3]).longValue());
            }
            dto.setRfsCode((String)obj[4]);
            dto.setRfsName((String)obj[5]);
            dto.setMacName((String)obj[6]);
            if(obj[7] != null){
            	dto.setIsVirtualDealer((Long.parseLong(obj[7] + "")));//add 2012-6-30
            }else{
            	dto.setIsVirtualDealer(0L);
            }
            listDto.add(dto);
        }
       
        return listDto;
    }

    /** 
     * overridden:
     * @Date        :      2011-6-17
     * @see com.cap.bts.gdms.dao.IDealerMacDao#queryMacWithPaging(com.cap.bts.gdms.dto.MacQueryDto)
    **/
    @Override
    public List<MacQueryResultDto> queryMacWithPaging(MacQueryDto macQueryDto) {
        String countHql = "select count(*) from Organization a where a.parentOrgCode = '"
            + macQueryDto.getRfsCode() + "' and a.orgType = " + OrganizationType.MAC + " \n";

        String hql = "select new com.cap.bts.framework.common.dto.MacQueryResultDto(a.id, a.orgCode, a.orgName) \n"
            + "from Organization a where a.parentOrgCode = '" + macQueryDto.getRfsCode()
            + "' and a.orgType = " + OrganizationType.MAC + " \n";

        StringBuffer sb = new StringBuffer(500);
        if (Utils.notEmpty(macQueryDto.getMacCode())) {
            sb.append(" and a.orgCode = '" + macQueryDto.getMacCode() + "'");
        }
        if (Utils.notEmpty(macQueryDto.getMacName())) {
            sb.append(" and a.orgName like '" + macQueryDto.getMacName() + "%'");
        }

        logger.debug("List<MacQueryResultDto> query countHql: " + countHql + sb.toString());
        logger.debug("List<MacQueryResultDto> query hql: " + hql + sb.toString());

        List<MacQueryResultDto> listDto = getJpaTemplate().executeFind(
            new QueryWithPagingJpaCallback<MacQueryResultDto>(hql + sb.toString(), countHql
                + sb.toString(), macQueryDto.getPager()));
        return listDto;
    }

    public int queryCountBillboardByDealerID(String querySql)throws AppException
    {
       int totalValue= this.countByNativeQuery(querySql); 
       
       return totalValue;
    }

    
    
}
